package KC.b2b.conciliacion.manual;

import java.sql.*;
import java.sql.Date;
import java.text.*;
import java.util.*;

public class ConciliacionB {
  private String str_mes;
  private int int_anio;

  private String str_cuentaBanco;
  private double dbl_saldoInicialBanco;
  private double dbl_saldoFinalBanco;
  private double dbl_conciliadoBanco;
  private double dbl_sinConciliarBanco;

  private String str_cuentaBaan;
  private double dbl_saldoInicialBaaN;
  private double dbl_saldoFinalBaaN;
  private double dbl_conciliadoBaaN;
  private double dbl_sinConciliarBaaN;
  private String str_nombreBanco;
  //private int int_estatus;

  private static String [] strs_mes = {"Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre"};
  private static NumberFormat nf = NumberFormat.getInstance();
  static{
    nf.setMaximumFractionDigits(2);
    nf.setMinimumFractionDigits(2);
  }

  private static SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
  private int int_mes;
  private int int_cuentaId;

  private ConciliacionB( ){
  }

  protected int guardarPartidaManual( Statement stmt_guardar, int baanId, int bancoId, int cuentaId, int mes, int anio, String str_comentario, int int_status, int int_tipoTransaccion, int int_baanBanco )throws SQLException{
    String sql_guardar =
        " exec sp_insertarDetalleConciliacionManual "+cuentaId+", "+mes+", "+anio+", 1, "+bancoId+", "+baanId+", '"+str_comentario+"', "+int_status+", "+int_tipoTransaccion+", "+int_baanBanco+"";
    ResultSet rs_guardar = stmt_guardar.executeQuery(sql_guardar);
    int int_idDetalleConciliacion = 0;
    if( rs_guardar.next() ){
      int_idDetalleConciliacion = rs_guardar.getInt("idDetalleConciliacion");
    }
    stmt_guardar.close();
    return int_idDetalleConciliacion;
  }

  protected static void borrarPartidaManual( Statement stmt_borrar, int int_conciliacionId )throws SQLException{
    String sql_guardar =
        " exec sp_borrarDetalleConciliacion "+int_conciliacionId;
    stmt_borrar.executeUpdate(sql_guardar);
    stmt_borrar.close();
  }


  protected static Object[][] listar_notConciliadoBank(Connection conn,
                                               int int_idConciliacion) throws
      SQLException {

    Statement stmt_concilBank = conn.createStatement(ResultSet.
        TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    String str_concilBank =
        " SELECT	0 as slctd,                          " +
        "               recordid,                            " +
        "               fecha,                               " +
        "               referencia,                          " +
        "               importe,                             " +
        "               importe as importe_float,            " +
        "               CASE WHEN (t.PERIODO <> t.mespartida) OR (t.anyo <> t.aniopartida ) THEN 1 else 0 end AS anterior" +
        " FROM	arb2_conciliaciones c                        " +
        " INNER JOIN arb2_transacciones t                    " +
        "    ON c.anyo = t.anyo and                          " +
        "       c.mes = t.periodo and                        " +
        "       status in (1,3) and t.importe <> 0 and            " +
        "       c.cuentaid = t.cuentaid "+
        " WHERE	conciliacionid = " + int_idConciliacion + "  " +
        " ORDER BY 3 asc                             ";

    ResultSet rs_concilBank = stmt_concilBank.executeQuery(str_concilBank);
    ResultSetMetaData rsmd = rs_concilBank.getMetaData();

    Object[] obj_columns;
    Object[][] obj_data;

    rs_concilBank.last();
    if(rs_concilBank.getRow()!= -1){
      obj_columns = new Object[rsmd.getColumnCount()];
      obj_data = new Object[rs_concilBank.getRow()][rsmd.getColumnCount() + 1];
      rs_concilBank.first();

      for (int i = 0; i < obj_columns.length; i++) {
        obj_columns[i] = (String) rsmd.getColumnLabel(i + 1);
      }

      for (int i = 0; i < obj_data.length; i++) {
        for (int j = 0; j < obj_columns.length; j++) {
          switch (j) {
            case 0:
              obj_data[i][j] = (j == 0) ?
                  (new Boolean(rs_concilBank.getBoolean( (String) obj_columns[j]))) :
                  (rs_concilBank.getObject( (String) obj_columns[j]));
              break;
            case 1:
              obj_data[i][j] = rs_concilBank.getObject( (String) obj_columns[j]);
              break;
            case 2:
              Calendar cal_fecha = Calendar.getInstance();
              java.util.Date date_fecha = rs_concilBank.getDate( (String)
                  obj_columns[j]);
              cal_fecha.setTime(date_fecha);
              obj_data[i][j] = sdf.format(cal_fecha.getTime());
              break;
            case 3:
              obj_data[i][j] = rs_concilBank.getObject( (String) obj_columns[j]);
              break;
            case 4:
              double dbl_importe = rs_concilBank.getDouble( (String)
                  obj_columns[j]);
              obj_data[i][j] = nf.format(dbl_importe);
              break;
            case 5:
              obj_data[i][j] = new Double(rs_concilBank.getDouble( (String) obj_columns[j]));
              break;
            case 6:
              obj_data[i][j] = new Boolean(rs_concilBank.getBoolean((String) obj_columns[j]));
              break;
          }
        }
        rs_concilBank.next();
      }
    }else{
      obj_columns = new Object[0];
      obj_data = new Object[0][0];
    }
    return obj_data;
  }

  protected static Object[][] listar_notConciliadoBaan(Connection conn,
                                               int int_idConciliacion) throws
      SQLException {

    Statement stmt_concilBaan = conn.createStatement(ResultSet.
        TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    String str_concilBaan =
        " SELECT	0 as slctd,                         " +
        "               t.recordid,                         " +
        "               t.t_dcdt,                           " +
        "               t.t_refr,                           " +
        "               ( case when t_dbcr = 1 then -1 else 1 end ) * t_amnt as t_amnt, " +
        "               ( case when t_dbcr = 1 then -1 else 1 end ) * t_amnt as importe_float, " +
        "                 case when (t.T_FPRD <> t.mespartida) OR (T_FYER <> t.aniopartida) then 1 else 0 end as anterior " +
        " FROM	arb2_conciliaciones c                       " +
        " INNER JOIN arb2_tfgld106 t                        " +
        "         ON c.anyo = t.t_fyer AND                  " +
        "            c.mes = t.t_fprd AND                   " +
        "            status in (1,3) and t.t_amnt <> 0           " +
        " INNER JOIN arb2_cuentas cu                        " +
        "         ON cu.cuentabaan = t.t_leac and           " +
        "            cu.recordid = c.cuentaid               " +
        " WHERE	conciliacionid = " + int_idConciliacion + " " +
        " ORDER BY 3 asc                            ";

    ResultSet rs_concilBaan = stmt_concilBaan.executeQuery(str_concilBaan);
    ResultSetMetaData rsmd = rs_concilBaan.getMetaData();

    Object[] obj_columns;
    Object[][] obj_data;
    rs_concilBaan.last();
    if(rs_concilBaan.getRow()!= -1){


      obj_columns = new Object[rsmd.getColumnCount()];
      obj_data = new Object[rs_concilBaan.getRow()][rsmd.
          getColumnCount() + 1];
      rs_concilBaan.first();

      for (int i = 0; i < obj_columns.length; i++) {
        obj_columns[i] = (String) rsmd.getColumnLabel(i + 1);
      }

      for (int i = 0; i < obj_data.length; i++) {
        for (int j = 0; j < obj_columns.length; j++) {
          switch (j) {
            case 0:
              obj_data[i][j] = (j == 0) ?
                  (new Boolean(rs_concilBaan.getBoolean( (String) obj_columns[j]))) :
                  (rs_concilBaan.getObject( (String) obj_columns[j]));
              break;
            case 1:
              obj_data[i][j] = rs_concilBaan.getObject( (String) obj_columns[j]);
              break;
            case 2:
              Calendar cal_fecha = Calendar.getInstance();
              java.util.Date date_fecha = rs_concilBaan.getDate( (String)
                  obj_columns[j]);
              cal_fecha.setTime(date_fecha);
              obj_data[i][j] = sdf.format(cal_fecha.getTime());
              break;
            case 3:
              obj_data[i][j] = rs_concilBaan.getObject( (String) obj_columns[j]);
              break;
            case 4:
              double dbl_importe = rs_concilBaan.getDouble( (String)
                  obj_columns[j]);
              obj_data[i][j] = nf.format(dbl_importe);
              break;
            case 5:
              obj_data[i][j] = new Double(rs_concilBaan.getDouble( (String) obj_columns[j]))  ;
              break;
            case 6:
              obj_data[i][j] = new Boolean(rs_concilBaan.getBoolean((String) obj_columns[j]));
              break;
          }
        }
        rs_concilBaan.next();
      }
    }
    else{
      obj_columns = new Object[0];
      obj_data = new Object[0][0];
    }
    return obj_data;
  }

  public static Object [] conciliacionesDetalle( Connection conn, int int_idDetalleConciliacion )throws SQLException{
  ArrayList arrL_retVal = new ArrayList();

  Statement stmt_conciliaciones = conn.createStatement();

  String str_conciliaciones =
      " select recordid, " +
      "        fecha, " +
	  "        'BNK' AS docto, " +
      "        referencia, " +
      "        importe,  " +
      "        1 as tipo, "+
      "	dc.status  " +
      " from	arb2_detalleconciliaciones dc " +
      "                inner join arb2_transacciones t " +
      "                on dc.idtxconcilia = t.idtxconcilia " +
      " where	dc.idtxconcilia = "+int_idDetalleConciliacion+" " +
      " union all " +
      " select recordid, " +
      "        t_dcdt, " +
	  "        rtrim(t_otyp) + rtrim(t_odoc) AS docto, " +
      "        t_refr, " +
      "        ( case when t_dbcr = 1 then -1 else 1 end ) * t_amnt as t_amnt, " +
      "        2 as tipo, " +
      "	dc.status   " +
      " from	arb2_detalleconciliaciones dc " +
      "                inner join arb2_tfgld106 t " +
      "                on dc.idtxconcilia = t.idtxconcilia " +
      " where	dc.idtxconcilia = "+int_idDetalleConciliacion+" ";

  ResultSet rs_conciliaciones = stmt_conciliaciones.executeQuery(str_conciliaciones);

  int int_idtxconcilia = int_idDetalleConciliacion;
  int int_id = -1;
  int int_status = -1;
  Date dte_fecha;
  Calendar cal_fecha;
  String str_docto;
  String str_referencia;
  double dbl_importe;
  int int_tipo;
  ArrayList arrL_elementosDetalleBanco = new ArrayList();
  ArrayList arrL_elementosDetalleBaaN = new ArrayList();

  while( rs_conciliaciones.next() ){

    ConciliacionElementoDetalle elementoDet = new ConciliacionElementoDetalle();

    int_id = rs_conciliaciones.getInt("recordid");
    dte_fecha = rs_conciliaciones.getDate("fecha");
    cal_fecha = Calendar.getInstance();
    cal_fecha.setTime(dte_fecha);
	str_docto = rs_conciliaciones.getString("docto");
    str_referencia = rs_conciliaciones.getString("referencia");
    str_referencia = str_referencia.length() > 27 ? str_referencia.substring(0, 24)+"..." : str_referencia;
    dbl_importe = rs_conciliaciones.getDouble("importe");
    int_tipo = rs_conciliaciones.getInt("tipo");
    int_status = rs_conciliaciones.getInt("status");

    elementoDet.setInt_id(int_id);
    elementoDet.setCal_fecha(cal_fecha);
	elementoDet.setStr_docto(str_docto.trim());
    elementoDet.setStr_referencia(str_referencia.trim());
    elementoDet.setDbl_importe(dbl_importe);
    elementoDet.setInt_tipo(int_tipo);

    if( int_tipo == ConciliacionElementoDetalle.BANCO ){
      arrL_elementosDetalleBanco.add(elementoDet);
    }else{
      arrL_elementosDetalleBaaN.add(elementoDet);
    }
  }

  if( int_id != -1 ){
    ConciliacionDetalle detalle = new ConciliacionDetalle();
    detalle.setInt_coniliacionId(int_idtxconcilia);
    detalle.setInt_status(int_status);
    detalle.setArrL_detalles(arrL_elementosDetalleBanco, arrL_elementosDetalleBaaN);
    arrL_retVal.add(detalle);
  }

  rs_conciliaciones.close();
  stmt_conciliaciones.close();
  return arrL_retVal.toArray();
}






public static Object [] conciliaciones( Connection conn, int int_conciliacionId )throws SQLException{
  ArrayList arrL_retVal = new ArrayList();

  Statement stmt_conciliaciones = conn.createStatement();

  String str_conciliaciones =
      " select dc.idtxconcilia, "+
      "        recordid, " +
      "        fecha, " +
	  "        'BNK' AS docto, " +
      "        referencia, " +
      "        importe,  " +
      "        1 as tipo, " +
      "        dc.status " +
      " from	arb2_conciliaciones c " +
      "                inner join arb2_detalleconciliaciones dc " +
      "                on c.conciliacionid = dc.conciliacionid " +
      "                inner join arb2_transacciones t " +
      "                on dc.idtxconcilia = t.idtxconcilia " +
      " where	c.conciliacionid = "+int_conciliacionId+" " +
      " union all " +
      " select dc.idtxconcilia, " +
      "        recordid, " +
      "        t_dcdt, " +
	  "        rtrim(t_otyp) + rtrim(t_odoc) AS docto, " +
      "        t_refr, " +
      "        ( case when t_dbcr = 1 then -1 else 1 end ) * t_amnt as t_amnt, " +
      "        2 as tipo, " +
      "        dc.status " +
      " from	arb2_conciliaciones c " +
      "                inner join arb2_detalleconciliaciones dc " +
      "                on c.conciliacionid = dc.conciliacionid " +
      "                inner join arb2_tfgld106 t " +
      "                on dc.idtxconcilia = t.idtxconcilia " +
      " where	c.conciliacionid = "+int_conciliacionId+" " +
      " order by dc.idtxconcilia ";

  ResultSet rs_conciliaciones = stmt_conciliaciones.executeQuery(str_conciliaciones);

  int int_idtxconcilia;
  int int_idtxconciliaAnt = -1;
  int int_id;
  int int_status = -1;
  int int_statusAnt = -1;
  Date dte_fecha;
  Calendar cal_fecha; 
  String str_docto;
  String str_referencia;
  double dbl_importe;
  int int_tipo;
  ArrayList arrL_elementosDetalleBanco = new ArrayList();
  ArrayList arrL_elementosDetalleBaaN = new ArrayList();

  while( rs_conciliaciones.next() ){
    int_idtxconcilia = rs_conciliaciones.getInt("idtxconcilia");
    int_status = rs_conciliaciones.getInt("status");

    if( int_idtxconciliaAnt != int_idtxconcilia && int_idtxconciliaAnt > -1 ){
      ConciliacionDetalle detalle = new ConciliacionDetalle();
      detalle.setInt_coniliacionId(int_idtxconciliaAnt);
      detalle.setArrL_detalles(arrL_elementosDetalleBanco, arrL_elementosDetalleBaaN);
      detalle.setInt_status(int_statusAnt);
      arrL_elementosDetalleBanco = new ArrayList();
      arrL_elementosDetalleBaaN = new ArrayList();
      arrL_retVal.add(detalle);
    }

    ConciliacionElementoDetalle elementoDet = new ConciliacionElementoDetalle();

    int_id = rs_conciliaciones.getInt("recordid");
    dte_fecha = rs_conciliaciones.getDate("fecha");
    cal_fecha = Calendar.getInstance();
    cal_fecha.setTime(dte_fecha);
	str_docto = rs_conciliaciones.getString("docto");
    str_referencia = rs_conciliaciones.getString("referencia");
    str_referencia = str_referencia.length() > 27 ? str_referencia.substring(0, 24)+"..." : str_referencia;
    dbl_importe = rs_conciliaciones.getDouble("importe");
    int_tipo = rs_conciliaciones.getInt("tipo");


    elementoDet.setInt_id(int_id);
    elementoDet.setCal_fecha(cal_fecha);
	elementoDet.setStr_docto(str_docto.trim());
    elementoDet.setStr_referencia(str_referencia.trim());
    elementoDet.setDbl_importe(dbl_importe);
    elementoDet.setInt_tipo(int_tipo);

    if( int_tipo == ConciliacionElementoDetalle.BANCO ){
      arrL_elementosDetalleBanco.add(elementoDet);
    }else{
      arrL_elementosDetalleBaaN.add(elementoDet);
    }
    int_statusAnt = int_status;
    int_idtxconciliaAnt = int_idtxconcilia;
  }

  if(int_idtxconciliaAnt>-1){
    ConciliacionDetalle detalle = new ConciliacionDetalle();
    detalle.setInt_status(int_statusAnt);
    detalle.setInt_coniliacionId(int_idtxconciliaAnt);
    detalle.setArrL_detalles(arrL_elementosDetalleBanco, arrL_elementosDetalleBaaN);
    arrL_retVal.add(detalle);
  }

  rs_conciliaciones.close();
  stmt_conciliaciones.close();
  return arrL_retVal.toArray();
}


  public static ConciliacionB instancia( Statement stmt_instancia, int int_conciliacionId )throws SQLException{
    ConciliacionB connB_retVal = new ConciliacionB();
    String str_instancia =
        " select mes, "+
        "        c.anyo, " +
        "        c.cuentaid, "+
        "        arb2_bancos.description, "+
        "        cuentabanco,  " +
        "        saldoinicial as saldo_apertura_banco, " +
        "        saldofinal as saldo_cierre_banco, " +
        "        cuentabaan, " +
        "        ARB2_TFGLD206_ANT.fobh as saldo_apertura_baan, " +
        "        ARB2_TFGLD206.fobh as saldo_cierre_baan, " +
        "        dbo.fnc_totalConciSin( mes, c.anyo, 0, 'arb2_transacciones' ) as totalSinConciliarBanco, " +
        "        dbo.fnc_totalConciSin( mes, c.anyo, 1, 'arb2_transacciones' ) as totalConciliadoBanco, " +
        "        dbo.fnc_totalConciSin( mes, c.anyo, 0, 'arb2_tfgld106' ) as totalSinConciliarBaaN, " +
        "        dbo.fnc_totalConciSin( mes, c.anyo, 1, 'arb2_tfgld106' ) as totalConciliadoBaaN " +
        " from	arb2_conciliaciones c " +
        "                inner join arb2_cuentas " +
        "                on cuentaid = arb2_cuentas.recordid " +
        "                inner join arb2_bancos " +
        "		 on bancoid = arb2_bancos.recordid "+
        "                left join ARB2_TFGLD206 " +
        "                on ARB2_TFGLD206.leac = cuentabaan and " +
        "                   ARB2_TFGLD206.[month] = mes and " +
        "                   ARB2_TFGLD206.[year] = anyo and " +
        "                   ARB2_TFGLD206.ccur = arb2_cuentas.divisa " +
        "                left join ARB2_TFGLD206 as ARB2_TFGLD206_ANT " +
        "                on ARB2_TFGLD206_ANT.leac = cuentabaan and " +
        "                   ARB2_TFGLD206_ANT.[month] = ( case when mes = 1 then 12 else mes - 1 end ) and " +
        "                   ARB2_TFGLD206_ANT.[year] = (case when mes =  1 then anyo -1 else anyo end ) and " +
        "                   ARB2_TFGLD206_ANT.ccur = arb2_cuentas.divisa " +
        " where	conciliacionid = "+int_conciliacionId+" ";
    ResultSet rs_instancia = stmt_instancia.executeQuery(str_instancia);
    int int_mesTmp;
    if( rs_instancia.next() ){
      connB_retVal.setStr_nombreBanco(rs_instancia.getString("description"));
      connB_retVal.setInt_anio(rs_instancia.getInt("anyo"));
      int_mesTmp = rs_instancia.getInt("mes");
      connB_retVal.setStr_mes(strs_mes[int_mesTmp-1]);
      connB_retVal.setInt_mes(int_mesTmp);
      connB_retVal.setInt_cuentaId(rs_instancia.getInt("cuentaid"));

      connB_retVal.setStr_cuentaBanco(rs_instancia.getString("cuentabanco"));
      connB_retVal.setDbl_conciliadoBanco(rs_instancia.getDouble("totalConciliadoBanco"));
      connB_retVal.setDbl_saldoFinalBanco(rs_instancia.getDouble("saldo_cierre_banco"));
      connB_retVal.setDbl_saldoInicialBanco(rs_instancia.getDouble("saldo_apertura_banco"));
      connB_retVal.setDbl_sinConciliarBanco(rs_instancia.getDouble("totalSinConciliarBanco"));

      connB_retVal.setStr_cuentaBaan(rs_instancia.getString("cuentabaan"));
      connB_retVal.setDbl_conciliadoBaaN(rs_instancia.getDouble("totalConciliadoBaaN"));
      connB_retVal.setDbl_saldoFinalBaaN(rs_instancia.getDouble("saldo_cierre_baan"));
      connB_retVal.setDbl_saldoInicialBaaN(rs_instancia.getDouble("saldo_apertura_baan"));
      connB_retVal.setDbl_sinConciliarBaaN(rs_instancia.getDouble("totalSinConciliarBaaN"));
    }
    stmt_instancia.close();
    return connB_retVal;
  }

  public static Object[][] agregaFilaBaan(Connection conn, int int_conciliacionIdTmp) throws
      SQLException {
    String str_baan =
        "  SELECT 0 as slctd,                                             " +
        "         t.recordid,                                             " +
        "         t.t_dcdt,                                               " +
        "         t.t_refr,                                               " +
        "        ( case when t_dbcr = 1 then -1 else 1 end ) * t_amnt as t_amnt,  " +
        "        ( case when t_dbcr = 1 then -1 else 1 end ) * t_amnt as importe_float,  " +
        "        case when (t.T_FPRD <> t.mespartida) OR (T_FYER <> t.aniopartida) then 1 else 0 end as anterior  " +
        "  FROM	arb2_conciliaciones c                     " +
        "      INNER JOIN arb2_tfgld106 t                         " +
        "            ON c.anyo = t.t_fyer AND                   " +
        "               c.mes = t.t_fprd and t.t_amnt <> 0         " +
        "      INNER JOIN arb2_cuentas cu                         " +
        "            ON cu.cuentabaan = t.t_leac and            " +
        "               cu.recordid = c.cuentaid  " +
        "  WHERE t.idtxconcilia =  "+int_conciliacionIdTmp+"   ";
    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

    ResultSet rs_concilBaan = stmt.executeQuery(str_baan);
    ResultSetMetaData rsmd = rs_concilBaan.getMetaData();

    Object[] obj_columns;
    Object[][] obj_data;
    rs_concilBaan.last();
    if(rs_concilBaan.getRow()!= -1){


      obj_columns = new Object[rsmd.getColumnCount()];
      obj_data = new Object[rs_concilBaan.getRow()][rsmd.
          getColumnCount() + 1];
      rs_concilBaan.first();

      for (int i = 0; i < obj_columns.length; i++) {
        obj_columns[i] = (String) rsmd.getColumnLabel(i + 1);
      }

      for (int i = 0; i < obj_data.length; i++) {
        for (int j = 0; j < obj_columns.length; j++) {
          switch (j) {
            case 0:
              obj_data[i][j] = new Boolean(true);
              break;
            case 1:
              obj_data[i][j] = rs_concilBaan.getObject( (String) obj_columns[j]);
              break;
            case 2:
              Calendar cal_fecha = Calendar.getInstance();
              java.util.Date date_fecha = rs_concilBaan.getDate( (String)
                  obj_columns[j]);
              cal_fecha.setTime(date_fecha);
              obj_data[i][j] = sdf.format(cal_fecha.getTime());
              break;
            case 3:
              obj_data[i][j] = rs_concilBaan.getObject( (String) obj_columns[j]);
              break;
            case 4:
              double dbl_importe = rs_concilBaan.getDouble( (String)
                  obj_columns[j]);
              obj_data[i][j] = nf.format(dbl_importe);
              break;
            case 5:
              obj_data[i][j] = new Double(rs_concilBaan.getDouble( (String) obj_columns[j]))  ;
              break;
            case 6:
              obj_data[i][j] = new Boolean(rs_concilBaan.getBoolean((String) obj_columns[j]));
              break;
          }
        }
        rs_concilBaan.next();
      }
    }
    else{
      obj_columns = new Object[0];
      obj_data = new Object[0][0];
    }
    return obj_data;
  }


  public static Object[][] agregaFilaBanco(Connection conn, int int_conciliacionIdTmp) throws
    SQLException {

  String str_banco =
      " SELECT	0 as slctd, " +
      "         recordid,   " +
      "         fecha,   " +
      "         referencia, " +
      "         importe, " +
      "         importe as importe_float,  " +
      "         CASE WHEN (t.PERIODO <> t.mespartida) OR (t.anyo <> t.aniopartida ) THEN 1 else 0 end AS anterior  " +
      " FROM	arb2_conciliaciones c  " +
      "     INNER JOIN arb2_transacciones t " +
      "           ON c.anyo = t.anyo and " +
      "              c.mes = t.periodo and t.importe <> 0 and  " +
      "              c.cuentaid = t.cuentaid  " +
      " WHERE	t.idtxconcilia =  "+int_conciliacionIdTmp+"  " ;


  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

  ResultSet rs_concilBanco = stmt.executeQuery(str_banco);
  ResultSetMetaData rsmd = rs_concilBanco.getMetaData();

  Object[] obj_columns;
  Object[][] obj_data;
  rs_concilBanco.last();
  if(rs_concilBanco.getRow()!= -1){


    obj_columns = new Object[rsmd.getColumnCount()];
    obj_data = new Object[rs_concilBanco.getRow()][rsmd.
        getColumnCount() + 1];
    rs_concilBanco.first();

    for (int i = 0; i < obj_columns.length; i++) {
      obj_columns[i] = (String) rsmd.getColumnLabel(i + 1);
    }

    for (int i = 0; i < obj_data.length; i++) {
      for (int j = 0; j < obj_columns.length; j++) {
        switch (j) {
          case 0:
            obj_data[i][j] = new Boolean(true);
            break;
          case 1:
            obj_data[i][j] = rs_concilBanco.getObject( (String) obj_columns[j]);
            break;
          case 2:
            Calendar cal_fecha = Calendar.getInstance();
            java.util.Date date_fecha = rs_concilBanco.getDate( (String)
                obj_columns[j]);
            cal_fecha.setTime(date_fecha);
            obj_data[i][j] = sdf.format(cal_fecha.getTime());
            break;
          case 3:
            obj_data[i][j] = rs_concilBanco.getObject( (String) obj_columns[j]);
            break;
          case 4:
            double dbl_importe = rs_concilBanco.getDouble( (String)
                obj_columns[j]);
            obj_data[i][j] = nf.format(dbl_importe);
            break;
          case 5:
            obj_data[i][j] = new Double(rs_concilBanco.getDouble( (String) obj_columns[j]))  ;
            break;
          case 6:
            obj_data[i][j] = new Boolean(rs_concilBanco.getBoolean((String) obj_columns[j]));
            break;
        }
      }
      rs_concilBanco.next();
    }
  }
  else{
    obj_columns = new Object[0];
    obj_data = new Object[0][0];
  }
  return obj_data;
}


  public double getDbl_conciliadoBaaN() {
    return dbl_conciliadoBaaN;
  }

  public double getDbl_conciliadoBanco() {
    return dbl_conciliadoBanco;
  }

  public double getDbl_saldoFinalBaaN() {
    return dbl_saldoFinalBaaN;
  }

  public double getDbl_saldoFinalBanco() {
    return dbl_saldoFinalBanco;
  }

  public double getDbl_saldoInicialBaaN() {
    return dbl_saldoInicialBaaN;
  }

  public double getDbl_saldoInicialBanco() {
    return dbl_saldoInicialBanco;
  }

  public double getDbl_sinConciliarBaaN() {
    return dbl_sinConciliarBaaN;
  }

  public double getDbl_sinConciliarBanco() {
    return dbl_sinConciliarBanco;
  }

  public int getInt_anio() {
    return int_anio;
  }

  public String getStr_mes() {

    return str_mes;
  }

  public String getStr_cuentaBaan() {
    return str_cuentaBaan;
  }

  public String getStr_cuentaBanco() {
    return str_cuentaBanco;
  }

  public String getStr_nombreBanco() {
    return str_nombreBanco;
  }

  public int getInt_mes() {
    return int_mes;
  }

  public int getInt_cuentaId() {
    return int_cuentaId;
  }

  public static int getInt_estatus(Connection conn, int int_concilId) throws
      SQLException {
    String str_query=
        " SELECT statusid                              " +
        " FROM arb2_conciliaciones                    "+
        " WHERE conciliacionid = " + int_concilId + " ";
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(str_query);
    if(rs.next())
      return rs.getInt("statusid");
    return 0;
  }

  public String getStr_conciliadoBaaN() {
    return nf.format(dbl_conciliadoBaaN);
  }

  public String getStr_conciliadoBanco() {
    return nf.format(dbl_conciliadoBanco);
  }

  public String getStr_importeTotalBanco(){
    return nf.format(dbl_conciliadoBanco+dbl_sinConciliarBanco);
  }

  public String getStr_saldoFinalBaaN() {
    return nf.format(dbl_saldoFinalBaaN);
  }

  public String getStr_saldoFinalBanco() {
    return nf.format(dbl_saldoFinalBanco);
  }

  public String getStr_saldoInicialBaaN() {
    return nf.format(dbl_saldoInicialBaaN);
  }

  public String getStr_saldoInicialBanco() {
    return nf.format(dbl_saldoInicialBanco);
  }

  public String getStr_sinConciliarBaaN() {
    return nf.format(dbl_sinConciliarBaaN);
  }

  public String getStr_importeTotalBaaN( ){
    return nf.format(dbl_conciliadoBaaN+dbl_sinConciliarBaaN);
  }

  public String getStr_sinConciliarBanco() {
    return nf.format(dbl_sinConciliarBanco);
  }

  public void setStr_cuentaBanco(String str_cuentaBanco) {
    this.str_cuentaBanco = str_cuentaBanco;
  }

  public void setStr_cuentaBaan(String str_cuentaBaan) {
    this.str_cuentaBaan = str_cuentaBaan;
  }

  public void setStr_mes(String str_mes) {

    this.str_mes = str_mes;
  }

  public void setInt_anio(int int_anio) {
    this.int_anio = int_anio;
  }

  public void setDbl_sinConciliarBanco(double dbl_sinConciliarBanco) {
    this.dbl_sinConciliarBanco = dbl_sinConciliarBanco;
  }

  public void setDbl_sinConciliarBaaN(double dbl_sinConciliarBaaN) {
    this.dbl_sinConciliarBaaN = dbl_sinConciliarBaaN;
  }

  public void setDbl_saldoInicialBaaN(double dbl_saldoInicialBaaN) {
    this.dbl_saldoInicialBaaN = dbl_saldoInicialBaaN;
  }

  public void setDbl_saldoFinalBanco(double dbl_saldoFinalBanco) {
    this.dbl_saldoFinalBanco = dbl_saldoFinalBanco;
  }

  public void setDbl_saldoFinalBaaN(double dbl_saldoFinalBaaN) {
    this.dbl_saldoFinalBaaN = dbl_saldoFinalBaaN;
  }

  public void setDbl_conciliadoBanco(double dbl_conciliadoBanco) {
    this.dbl_conciliadoBanco = dbl_conciliadoBanco;
  }

  public void setDbl_conciliadoBaaN(double dbl_conciliadoBaaN) {
    this.dbl_conciliadoBaaN = dbl_conciliadoBaaN;
  }

  public void setDbl_saldoInicialBanco(double dbl_saldoInicialBanco) {
    this.dbl_saldoInicialBanco = dbl_saldoInicialBanco;
  }

  public void setStr_nombreBanco(String str_nombreBanco) {
    this.str_nombreBanco = str_nombreBanco;
  }

  public void setInt_mes(int int_mes) {
    this.int_mes = int_mes;
  }

  public void setInt_cuentaId(int int_cuentaId) {
    this.int_cuentaId = int_cuentaId;
  }

}
